﻿using Dapper;
using OMG.Fill.Potato.Model;
using OMG.Fill.Potato.Model.Ent;
using OMG.Fill.Potato.Service;
using ProjectShare;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
namespace OMG.Fill.Potato.Services
{
    /// <summary>
    /// 填报基本信息T_Report通用操作类
    /// </summary>
    public class ReportServices : Reports
    {
        /// <summary>
        /// 填报数据读取
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public Pager<List<ReportModel>> GetPageList(ReportQuery query)
        {
            AreaServices areaService = new AreaServices();
            Pager<List<ReportModel>> obj = new Pager<List<ReportModel>>();            
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            if (!string.IsNullOrEmpty(query.FillInYear))
            {               
                strWhereSql += "and FillInYear = @FillInYear ";
                paramlist.Add("FillInYear", query.FillInYear);
            }
            if (!string.IsNullOrEmpty(query.TimesType))
            {
                strWhereSql += "and TimesType = @TimesType ";
                paramlist.Add("TimesType", query.TimesType);
            }
            if (!string.IsNullOrEmpty(query.TimesCycle)&&query.TimesCycle!="0")
            {
                strWhereSql += "and TimesCycle = @TimesCycle ";
                paramlist.Add("TimesCycle", query.TimesCycle);
            }
            if (!string.IsNullOrEmpty(query.Status)&&query.Status!="999")
            {
                strWhereSql += "and Status = @Status ";
                paramlist.Add("Status", query.Status);
            }
            //前端默认设置
            if (!string.IsNullOrEmpty(query.ReportCode))
            {
                strWhereSql += "and ReportCode = @ReportCode ";
                paramlist.Add("ReportCode", query.ReportCode);
            }
            //前端默认设置
            if (!string.IsNullOrEmpty(query.AreaCode))
            {

                if (query.AreaCode.Substring(2, 10).Equals("0000000000"))//省级账号
                {
                    strWhereSql += "and AreaCode like @AreaCode ";
                    paramlist.Add("AreaCode", "%" + query.AreaCode.Substring(0, 2) + "%");
                }
                else if (query.AreaCode.Substring(4, 8).Equals("00000000"))//州级账号
                {
                    strWhereSql += "and AreaCode like @AreaCode ";
                    paramlist.Add("AreaCode", "%" + query.AreaCode.Substring(0, 4) + "%");
                }
                else
                {
                    strWhereSql += "and AreaCode = @AreaCode ";
                    paramlist.Add("AreaCode", query.AreaCode);
                }
            }
            string strFromSql = "from dbo.T_Report where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = " order by TimesCycle asc ";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow " + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<ReportModel>(strSql, paramlist).ToList();
                    
                    foreach (var item in obj.rows)
                    {                        
                        item.StatusName = string.IsNullOrEmpty(item.Status) ? "" : DataDict.Instance["ReportStatus"][item.Status];
                        item.FillInYearName = string.IsNullOrEmpty(item.FillInYear) ? "" : DataDict.Instance["ReportYear"][item.FillInYear];
                        item.ReportCodeName = string.IsNullOrEmpty(item.ReportCode) ? "" : DataDict.Instance["ReportTableName"][item.ReportCode];
                        item.TimesTypeName = string.IsNullOrEmpty(item.TimesType) ? "" : DataDict.Instance["ReportCycle"][item.TimesType];                       
                        switch (item.TimesType)
                        {
                            case "1":
                                item.TimesCycleName = item.FillInYearName;
                                break;
                            case "4":                               
                                item.TimesCycleName = string.IsNullOrEmpty(item.TimesType) ? "" : DataDict.Instance["QuarterCycleValue"][item.TimesCycle];
                                break;
                            case "12":
                                item.TimesCycleName = string.IsNullOrEmpty(item.TimesType) ? "" : DataDict.Instance["MonthCycleValue"][item.TimesCycle];
                                break;
                        }
                        item.AreaCodeName = string.IsNullOrEmpty(item.AreaCode) ? "" : areaService.GetByCodeModel(item.AreaCode).model.Name;
                        item.StrAddTime = item.AddTime == null ? "" : item.AddTime.Value.ToString("yyyy-MM-dd hh:ss:mm");
                        item.StrUpdateTime = item.UpdateTime == null ? "" : item.UpdateTime.Value.ToString("yyyy-MM-dd hh:ss:mm");
                    }
                    obj.total = dbCon.Query<int>(strTotal, paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取填报基本信息T_Report分页列表失败", ex);
                return obj;
            }
        }

        //public ResResult<List<ReportReview>> GetReportReview(string areaCode,string FillInYear)
        //{
        //    using (var dbCon = ConnectFactory.GetOpenConnection())
        //    {
        //        ResResult<List<ReportReview>> obj = new ResResult<List<ReportReview>>();
        //        var paramlist = new DynamicParameters();
        //        string strSql = "select ReportName,sort,ISNULL(a.[0], 0) as 'ReviewFalse' ,";
        //        strSql += "ISNULL(a.[1], 0) as 'NotFillIn' ,ISNULL(a.[2], 0) as 'FillIn' ,";
        //        strSql += "ISNULL(a.[3], 0) as 'AlredayReport' ,ISNULL(a.[4], 0) as 'ReviewSuccess' ,";
        //        strSql += "ISNULL(a.[5], 0) as 'Lock'from(";
        //        strSql += "select reportName,sort,Status, COUNT(1) RS from [T_Report]";
        //        strSql += " where FillInYear = @FillInYear  ";
        //        paramlist.Add("FillInYear", FillInYear);
        //        if (!string.IsNullOrEmpty(areaCode) && areaCode != "null")
        //        {
        //            //paramlist.Add("AreaCode", areaCode);
        //            //strSql += " where AreaCode = @AreaCode ";
        //            if (areaCode.Substring(2, 10).Equals("0000000000"))//省级账号
        //            {
        //                strSql += "and AreaCode like @AreaCode ";
        //                paramlist.Add("AreaCode", "%" + areaCode.Substring(0, 2) + "%");
        //            }
        //            else if (areaCode.Substring(4, 8).Equals("00000000"))//州级账号
        //            {
        //                strSql += "and AreaCode like @AreaCode ";
        //                paramlist.Add("AreaCode", "%" + areaCode.Substring(0, 4) + "%");
        //            }
        //            else
        //            {
        //                strSql += "and AreaCode = @AreaCode ";
        //                paramlist.Add("AreaCode", areaCode);
        //            }
        //        }
        //        strSql += " group by reportName,[Status],sort";
        //        strSql += " ) t";
        //        strSql += " pivot(max(t.RS) FOR t.[Status] IN ([0] , [1], [2], [3], [4], [5])) a";
        //        try
        //        {
        //            obj.model = dbCon.Query<ReportReview>(strSql, paramlist).ToList();
        //            obj.status = DefaultKey.SuccessResult;
        //            obj.message = "任务完成结果获取成功";
        //        }
        //        catch (Exception ex)
        //        {
        //            obj.status = DefaultKey.ErrorResult;
        //            obj.message = "任务完成结果获取失败" + ex.ToString().Trim();
        //        }
        //        dbCon.Dispose();//数据库连接释放
        //        return obj;
        //    }
        //}
        public ResResult<List<ReportReview>> GetReportReview(string areaCode, string FillInYear)
        {
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                ResResult<List<ReportReview>> obj = new ResResult<List<ReportReview>>();
                var paramlist = new DynamicParameters();
                string strSql = "select ReportName,Sort,TimesCycle,ISNULL(a.[0], 0) as 'ReviewFalse' ,";
                strSql += "ISNULL(a.[1], 0) as 'NotFillIn' ,ISNULL(a.[2], 0) as 'FillIn' ,";
                strSql += "ISNULL(a.[3], 0) as 'AlredayReport' ,ISNULL(a.[4], 0) as 'ReviewSuccess' ,";
                strSql += "ISNULL(a.[5], 0) as 'Lock'from(";
                strSql += "select t1.ReportName,t1.Status,t1.TimesCycle,t2.Sort,COUNT(1) RS from T_Report t1 join T_Dict t2 on t1.ReportCode = t2.DictValue  ";
                strSql += "where GroupCode = 'ReportTableName' ";
                if (!string.IsNullOrEmpty(areaCode) && areaCode != "null")
                {
                    //paramlist.Add("AreaCode", areaCode);
                    //strSql += " where AreaCode = @AreaCode ";
                    if (areaCode.Substring(2, 10).Equals("0000000000"))//省级账号
                    {
                        strSql += "And AreaCode like @AreaCode ";
                        paramlist.Add("AreaCode", "%" + areaCode.Substring(0, 2) + "%");
                    }
                    else if (areaCode.Substring(4, 8).Equals("00000000"))//州级账号
                    {
                        strSql += "And AreaCode like @AreaCode ";
                        paramlist.Add("AreaCode", "%" + areaCode.Substring(0, 4) + "%");
                    }
                    else
                    {
                        strSql += "And AreaCode = @AreaCode ";
                        paramlist.Add("AreaCode", areaCode);
                    }
                }
                if (!string.IsNullOrEmpty(FillInYear))
                {
                    string year = FillInYear.Substring(0, 4);
                    strSql += "And FillInYear = @FillInYear";
                    paramlist.Add("FillInYear", year);
                }
                strSql += "  group by t1.ReportName,t1.Status,t2.Sort,t1.TimesCycle";
                strSql += " ) t";
                strSql += " pivot(max(t.RS) FOR t.[Status] IN ([0] , [1], [2], [3], [4], [5])) a order by Sort,TimesCycle";
                try
                {
                    obj.model = dbCon.Query<ReportReview>(strSql, paramlist).ToList();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "任务完成结果获取成功";
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = "任务完成结果获取失败" + ex.ToString().Trim();
                }
                dbCon.Dispose();//数据库连接释放
                return obj;
            }
        }

        public ResResult<List<ReportDetailReview>> GetDetailReportReview(ReportModel model)
        {
            using (var dbCon = ConnectFactory.GetOpenConnection())
            {
                ResResult<List<ReportDetailReview>> obj = new ResResult<List<ReportDetailReview>>();
                var paramlist = new DynamicParameters();
                string strSql = "select ta.Name CityName,tt.CountyName,tt.Status from T_Area ta join ";
                strSql += "(select t1.AreaCode,t2.Name CountyName,t1.Status,t2.ParentCode from T_Report t1 join T_Area t2 on t1.AreaCode = t2.Code ";
                strSql += " where t1.ReportName = @ReportName ";
                paramlist.Add("ReportName", model.ReportName);
                if (!string.IsNullOrEmpty(model.AreaCode))
                {
                    string areaCode = model.AreaCode;
                    if (areaCode.Substring(2, 10).Equals("0000000000"))//省级账号
                    {
                        strSql += "And AreaCode like @AreaCode ";
                        paramlist.Add("AreaCode", "%" + areaCode.Substring(0, 2) + "%");
                    }
                    else if (areaCode.Substring(4, 8).Equals("00000000"))//州级账号
                    {
                        strSql += "And AreaCode like @AreaCode ";
                        paramlist.Add("AreaCode", "%" + areaCode.Substring(0, 4) + "%");
                    }
                    else
                    {
                        strSql += "And AreaCode = @AreaCode ";
                        paramlist.Add("AreaCode", areaCode);
                    }
                }
                if (!string.IsNullOrEmpty(model.Status)&& !model.Status.Equals("999"))
                {
                    strSql += "  And Status = @Status";
                    paramlist.Add("Status", model.Status);
                }
                strSql += ")tt on tt.ParentCode = ta.Code";
                try
                {
                    obj.model = dbCon.Query<ReportDetailReview>(strSql, paramlist).ToList();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "任务完成结果获取成功";
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = "任务完成结果获取失败" + ex.ToString().Trim();
                }
                dbCon.Dispose();//数据库连接释放
                return obj;
            }
        }
        /// <summary>
        /// 审核列表界面
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public Pager<List<ReportModel>> GetReviewPageList(ReportQuery query)
        {
            AreaServices areaService = new AreaServices();
            Pager<List<ReportModel>> obj = new Pager<List<ReportModel>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = " and Status >'2' ";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            if (!string.IsNullOrEmpty(query.FillInYear))
            {
                strWhereSql += "and FillInYear = @FillInYear ";
                paramlist.Add("FillInYear", query.FillInYear);
            }
            if (!string.IsNullOrEmpty(query.TimesType))
            {
                strWhereSql += "and TimesType = @TimesType ";
                paramlist.Add("TimesType", query.TimesType);
            }
            //前端默认设置
            if (!string.IsNullOrEmpty(query.ReportCode))
            {
                strWhereSql += "and ReportCode = @ReportCode ";
                paramlist.Add("ReportCode", query.ReportCode);
            }
            //前端默认设置
            if (!string.IsNullOrEmpty(query.AreaCode))
            {

                if (query.AreaCode.Substring(2, 10).Equals("0000000000"))//省级账号
                {
                    strWhereSql += "and AreaCode like @AreaCode ";
                    paramlist.Add("AreaCode", "%" + query.AreaCode.Substring(0, 2) + "%");
                }
                else if (query.AreaCode.Substring(4, 8).Equals("00000000"))//州级账号
                {
                    strWhereSql += "and AreaCode like @AreaCode ";
                    paramlist.Add("AreaCode", "%" + query.AreaCode.Substring(0, 4) + "%");
                }
                else
                {
                    strWhereSql += "and AreaCode = @AreaCode ";
                    paramlist.Add("AreaCode", query.AreaCode);
                }
            }
            string strFromSql = "from dbo.T_Report where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "AreaCode asc,TimesCycle asc, ";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by " + strOrderSql + " Id asc) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow ";
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<ReportModel>(strSql, paramlist).ToList();
                    foreach (var item in obj.rows)
                    {
                        item.StatusName = string.IsNullOrEmpty(item.Status) ? "" : DataDict.Instance["ReportStatus"][item.Status];
                        item.FillInYearName = string.IsNullOrEmpty(item.FillInYear) ? "" : DataDict.Instance["ReportYear"][item.FillInYear];
                        item.ReportCodeName = string.IsNullOrEmpty(item.ReportCode) ? "" : DataDict.Instance["ReportTableName"][item.ReportCode];
                        item.TimesTypeName = string.IsNullOrEmpty(item.TimesType) ? "" : DataDict.Instance["ReportCycle"][item.TimesType];
                        item.AreaCodeName = string.IsNullOrEmpty(item.AreaCode) ? "" : areaService.GetByCodeModel(item.AreaCode).model.Name;
                        item.StrAddTime = item.AddTime == null ? "" : item.AddTime.Value.ToString("yyyy-MM-dd hh:ss:mm");
                        item.StrUpdateTime = item.UpdateTime == null ? "" : item.UpdateTime.Value.ToString("yyyy-MM-dd hh:ss:mm");
                    }
                    obj.total = dbCon.Query<int>(strTotal, paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取填报基本信息T_Report分页列表失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 修改报表状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="Status"></param>
        /// <param name="strMsg"></param>
        /// <returns></returns>
        public ResResult<int> UpStatus(string id, string Status, string strMsg)
        {
            strMsg = string.IsNullOrEmpty(strMsg) ? "" : strMsg;
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_Report set ";
            strSql += "Status = @Status, ";
            strSql += "Opinion = @Opinion ";
            strSql += " where Id = @Id";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    int result = dbCon.Execute(strSql, new { Id = id, Status = Status, Opinion = strMsg });
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "更新成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "更新失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("更新填报基本信息T_Report对象信息失败", ex);
                return obj;
            }
        }

        public ReportDataModel GetReportData(string reportId, string reportCode)
        {
            ReportDataModel model = new ReportDataModel();
            switch (reportCode)
            {
                
            }
            return model;
        }
        
        public bool CheckRepCount(string reportId,string tablename)
        {
            
            string strSql = @"select count(1) from dbo."+tablename+" where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("获取马铃薯原种大户情况表T_PotOriginalGrower对象信息失败", ex);
                return true;
            }
        }

        public bool CheckRepCreate(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select count(1) from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";          
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表T_PotBigGrower没有生成过", ex);
                return true;
            }
        }

        public string GetReportId(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select * from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    return dbCon.Query<ReportEntity>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault().Id;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表没有生成过", ex);
                return null;
            }
        }

        public string GetReportStatus(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select * from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    var tt=  dbCon.Query<ReportEntity>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault().Status;
                    return tt;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表没有生成过", ex);
                return null;
            }
        }
        public ReportEntity GetReportView(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select * from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    var tt = dbCon.Query<ReportEntity>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault();
                    return tt;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表没有生成过", ex);
                return null;
            }
        }

    }
}